﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace Bamboo.Oracle
{
    /// <summary>
    /// Oracle数据库操作相关工具类
    /// </summary>
    public class OracleUtils
    {
        /// <summary>
        /// 执行select语句并返回查询结果。（短连接）
        /// </summary>
        /// <param name="hostName"></param>
        /// <param name="port"></param>
        /// <param name="serviceName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static System.Data.DataTable ExecSelectSql(string hostName, int port, string serviceName, string userName, string password, string sql)
        {
            string connectionString = string.Format("User Id={0};Password={1};" +
                "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4})))",
                userName, password, hostName, port, serviceName);
            var dt = new System.Data.DataTable();
            using (var conn = new OracleConnection(connectionString))
            using (var cmd = new OracleCommand(sql, conn))
            using (var ad = new OracleDataAdapter(cmd))
            {
                ad.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable的内容转成字符串，用于调试输出。
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToString(DataTable dt)
        {
            var s = new StringBuilder(8*1024);
            //测试输出
            foreach (DataColumn c in dt.Columns)
            {
                s.Append(c.ColumnName + "\t");
            }
            s.Append("\r\n");
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; ++i)
                {
                    s.Append(row[i].ToString() + "\t");
                }
                s.Append("\r\n");
            }
            return s.ToString();
        }
    }
}
